import pandas as pd
import numpy as np
import pymysql

# 读取文件  打开数据库连接
db = pymysql.connect(
    host="localhost",
    user="root",
    password="hui123456",
    db='dbtest'
)

# 使用cursor()方法获取操作游标
cursor = db.cursor()

sql = """
     SELECT 
            order2, 
            Product_ID, 
            Product_xg, 
            Product_BianMa, 
            Product_num, 
            Product_price, 
            liulianglaiyuan, 
            daren, 
            guanggao, 
            liuliangleixing, 
            liuliangqudao, 
            liuliangticai, 
            day, 
            yh_pingtai, 
            yh_daren, 
            shop, 
            order_zt, 
            order_sh, 
            fuai, 
            dianzhuguo 
    FROM doudian_day 
    WHERE month(day) = 9 
        AND day != "1998-01-01"
     """
# 使用execute方法执行SQL语句
cursor.execute(sql)
# fetchall():接收全部的返回结果行.
result = cursor.fetchall()

dou = pd.DataFrame(list(result))

dou.columns = [
    '订单id', '商品ID', '选购商品', '商家编码', '商品数量', '订单应付金额', '流量来源',
    '达人昵称', '广告渠道', '流量类型', '流量渠道', '流量体裁', '订单提交时间', '平台实际承担优惠金额',
    '达人实际承担优惠金额', '店铺', '订单状态', '售后状态', '福袋', '电煮锅'
]

dou['成交金额_含优惠'] = np.where(
    (
            (dou['订单状态'] == "已签收") |
            (dou['订单状态'] == "待发货") |
            (dou['订单状态'] == "已完成") |
            (dou['订单状态'] == "已发货") |
            (dou['订单状态'] == "已支付") |
            (dou['订单状态'] == "部分发货")
    ) &
    (dou['售后状态'] != "退款成功") &
    (dou['售后状态'] != "售后完成") &
    (dou['售后状态'] != "退款完成") &
    (dou['售后状态'] != "已全额退款"),
    dou['订单应付金额'] + dou['平台实际承担优惠金额'] + dou['达人实际承担优惠金额'], 0
)

# 计算成交订单数
dou['成交订单'] = np.where(
    (
            (dou['订单状态'] == "已签收") |
            (dou['订单状态'] == "待发货") |
            (dou['订单状态'] == "已完成") |
            (dou['订单状态'] == "已发货") |
            (dou['订单状态'] == "已支付") |
            (dou['订单状态'] == "部分发货")
    ) &
    (dou['售后状态'] != "退款成功") &
    (dou['售后状态'] != "售后完成") &
    (dou['售后状态'] != "退款完成") &
    (dou['售后状态'] != "已全额退款"),
    1, 0
)

# 计算退款金额（含优惠）
dou['退款金额_含优惠'] = np.where(
    (dou['售后状态'] == '退款成功') |
    (dou['售后状态'] == '退款完成') |
    (dou['售后状态'] == '已全额退款') |
    (dou['售后状态'] == '售后完成') |
    (dou['售后状态'] == '退货退款完成') |
    (dou['售后状态'] == '交易关闭'),
    dou['订单应付金额'] + dou['平台实际承担优惠金额'] + dou['达人实际承担优惠金额'],
    0
)

ld = pd.read_excel(r'G:\工作\2025年订单\9月\绩效\刘栋达人.xlsx', dtype={'抖音号名称': str})
lxx1 = pd.read_excel(r'G:\工作\2025年订单\9月\绩效\罗欣欣达人1店.xlsx', dtype={'抖音号名称': str})  # (桶装)（袋装）
lxx2 = pd.read_excel(r'G:\工作\2025年订单\9月\绩效\罗欣欣达人2店.xlsx', dtype={'抖音号名称': str})  # (土豆粉)

xhwh1 = pd.read_excel(r'G:\工作\2025年订单\9月\绩效\夕湖文化.xlsx', dtype={'抖音号名称': str})  # (土豆粉)
hn1 = pd.read_excel(r'G:\工作\2025年订单\9月\绩效\花妞.xlsx', dtype={'抖音号名称': str})  # (土豆粉)
tt1 = pd.read_excel(r'G:\工作\2025年订单\9月\绩效\团团.xlsx', dtype={'抖音号名称': str})  # (土豆粉)

z1 = pd.read_excel(r'G:\工作\2025年订单\9月\招商1.xlsx', dtype={'订单id': str})
z2 = pd.read_excel(r'G:\工作\2025年订单\9月\招商2.xlsx', dtype={'订单id': str})
z3 = pd.read_excel(r'G:\工作\2025年订单\9月\招商3.xlsx', dtype={'订单id': str})
z4 = pd.read_excel(r'G:\工作\2025年订单\9月\招商4.xlsx', dtype={'订单id': str})

y1 = pd.read_excel(r'G:\工作\2025年订单\9月\佣金1.xlsx', dtype={'订单id': str})
y2 = pd.read_excel(r'G:\工作\2025年订单\9月\佣金2.xlsx', dtype={'订单id': str})
y3 = pd.read_excel(r'G:\工作\2025年订单\9月\佣金3.xlsx', dtype={'订单id': str})
y4 = pd.read_excel(r'G:\工作\2025年订单\9月\佣金4.xlsx', dtype={'订单id': str})
y = pd.concat([y1, y2, y3, y4])

# 匹配佣金表，达人昵称
y_date = y.loc[:, ['订单id', '作者账号', '预估佣金支出']]
y_date['订单id'] = y_date['订单id'].str.strip()

dou_date = pd.merge(dou, y_date, on='订单id', how='left')
dou_date['达人昵称'] = np.where(dou_date['达人昵称'] == 'nan', dou_date['作者账号'], dou_date['达人昵称'])
# 招商表
z1_date = z1.loc[:, ['订单id', '出单机构', '预估服务费收入']]
z2_date = z2.loc[:, ['订单id', '出单机构', '预估服务费收入']]
z3_date = z3.loc[:, ['订单id', '出单机构', '预估服务费收入']]
z4_date = z4.loc[:, ['订单id', '出单机构', '预估服务费收入']]
zs = pd.concat([z1_date, z2_date, z3_date, z4_date])

dou_res = pd.merge(dou_date, zs, on='订单id', how='left')

# 各投流链接,达人
lc_lj = [
    '3752066905167495351',
    '3754479940293230816'
]  # 良辰链接

lc_dr = []  # 良辰达人

ld_lj = [
    '3524421208374397998',
    '3718871706278953300',
    '3764710268731523333']  # 刘栋单独链接

ld_dr = list(ld['抖音号名称'])

lxx1_lj = [
    '3702367630972289253',
    '3753012046921335096',
    '3701090938974765405',
    '3506957255406950391',
    '3520740088860524483',
    '3745946018349449594',
    '3758543932670214558',
    '3758958156538052831',
    '3775109632327024899',
    '3699396698997653933'
]  # (罗欣欣达人）

lxx1_dr = list(lxx1['抖音号名称'])  # 罗欣欣达人

lxx2_lj = [
    '3748189374425203566',
    '3751738608588030256',
    '3752292420973363354',
    '3737638082678227388',
    '3628547567660489014',
    '3694732441748111734',
    '3754689465759694891',
    '3712805099375034797',
    '3674502993446830541',
    '3766406205405266422'
]  # (土豆粉达人)

lxx2_dr = list(lxx2['抖音号名称'])  # 罗欣欣土豆粉达人

xhwh1_lj = [
    '3751738608588030256',
    '3687141961262825560',
    '3758958156538052831',
    '3723151364247912548',
    '3701090938974765405'
]  # 夕湖文化

xhwh1_dr = list(xhwh1['抖音号名称'])
hn1_lj = [
    '3701090938974765405',
    '3758958156538052831',
    '3687141961262825560',
    '3775109632327024899'
]  # 花妞千川——爆品侠

hn1_dr = list(hn1['抖音号名称'])

tt1_lj = [
    '3751738608588030256',
    '3758958156538052831',
    '3687141961262825560',
    '3737638082678227388',
    '3628547567660489014',
    '3674502993446830541',
    '3694732441748111734'
]  # 团团别吃了

tt1_dr = list(tt1['抖音号名称'])


def f(x, y, z, v, s, w, p):  # x: 链接 #y: 达人昵称 #z:选购商品 #v:广告渠道 #s:流量来源 #w:出单机构
    if w in ['武汉海大电子商务有限公司']:  # 罗欣欣运营账号
        return '嗨大'

    elif w in ['卓敏科技', '紫金优选', '瀚睿科技', '红利传媒', '来吉团队', '金莎传媒']:  # 罗欣欣运营账号
        return '冷漠'

    elif (x in ld_lj) and ((y in ld_dr) or (pd.isnull(y))):  # 去掉招商达人，加上自然单
        return '刘栋单独链接'
    elif (x in ['3751738608588030256']) and (y in ld_dr):
        return '刘栋'

    elif y in ['harry5']:
        return 'harry5'

    elif y in ['喜喜小助理（直播返场）']:
        return '喜喜投流'
    elif y in ['张喜喜']:
        return '喜喜投流大号'

    elif y in ['美食挖掘机🍜', '饱饱吃不饱', '金金会发光', '味蕾探险家', '饭桶爱说笑', '大馋丫头可可']:  # 罗欣欣运营账号
        return '罗欣欣账号'

    elif y in ['啦啦啦lalala', '怪咖美食记', '爱吃的光远', '空壳少年', '食光里的馋猫🐱', '宛禾食品甄选号']:  # 白鹏飞运营账号
        return '白鹏飞账号'
    elif (x in xhwh1_lj) and (y in xhwh1_dr):
        return '夕湖文化'

    elif (x in tt1_lj) and (y in tt1_dr):
        return '罗欣欣'

    elif (x in lxx1_lj) and (y in lxx1_dr):
        return '罗欣欣'

    elif (x in lxx2_lj) and (y in lxx2_dr):
        return '罗欣欣'
    elif (x in hn1_lj) and (y in hn1_dr):
        return '代投花妞'

    elif (x in ['3751738608588030256']) and (y in hn1_dr):
        return '花妞'
    elif (x in lc_lj):
        return '莨辰'
    elif (x in ['3768784577812889610']):
        return '木森'
    elif x in ['3735010733180715297', '3741925082977665083']:  # 罗欣欣单独链接
        return '罗欣欣账号'
    elif x in ['3723120728170692876', '3723119143235485902', '3741926701987398242']:  # 白鹏飞单独链接
        return '白鹏飞账号'

    elif x in ['3714480675638870403', '3741923461510070306', '3550782895822129982', '3526108849595662124',
               '3699396698997653933']:
        return '共用链接自然单'

    elif (v == '商品卡') and (('lcc' in z) or ('lxx' in z)):
        return '罗欣欣3店商品卡'

    elif (('lcc' in z) or ('lxx' in z)):
        return '罗欣欣账号'

    elif 'zs2' in z or ('zz' in z) or ('zs' in z):
        return '代播'
    elif y == '宛禾食品旗舰店直播间' or y == '宛禾牛宅':
        return '代播'
    elif y == '宛禾速食旗舰店自播间' or y == '宛禾食品旗舰店自播间':
        return '代播'

    elif y == '宛禾米线官方旗舰店' or ('z1' in z) or (' Z1' in z):
        return '自播1'
    elif y == '宛禾米线速食官方旗舰店' or y == '宛禾米线' or y == '宛禾速食官方旗舰店' or y == '宛禾速食直播间' or (
            'z3' in z) or (' Z3' in z):
        return '自播3'
    elif y == '宛禾米线速食旗舰店' or ('lsf' in z):
        return '螺蛳土豆粉直播间'
    elif y == '宛禾食品旗舰店' or ('z5' in z) or ('Z5' in z):
        return '自播5'
    elif y == '宛禾速食旗舰店' or y == '宛禾食品旗舰店甄选号' or ('z6' in z) or ('Z6' in z):
        return '自播6'
    elif y == '宛禾官方旗舰店' or ('z8' in z) or (' Z8' in z):
        return '自播8'
    elif p == '抖音1' and ('dsp' in z):
        return '自播1'
    elif p == '抖音2' and ('dsp' in z):
        return '自播5'
    elif p == '抖音3' and ('dsp' in z):
        return '自播8'
    elif p == '抖音4' and ('dsp' in z):
        return '螺蛳土豆粉直播间'

    elif (v == '商品卡'):  # 投流商品卡
        return '超级商品卡'

    elif pd.notnull(y):
        return '达人分发'
    elif s == '精选联盟':
        return '达人分发'

    else:
        return '自然单'


dou_res['投流账户'] = list(map(f,
                               list(dou_res['商品ID']),
                               list(dou_res['达人昵称']),
                               list(dou_res['选购商品']),
                               list(dou_res['广告渠道']),
                               list(dou_res['流量来源']),
                               list(dou_res['出单机构']),
                               list(dou_res['店铺']))
                           )


def m(x, y, z, v, s, w):  # x: 链接 #y: 达人昵称 #z:选购商品 #v:广告渠道 #s:流量来源 #w:出单机构
    if w in ['武汉海大电子商务有限公司']:
        return '外部团队达人分发'

    elif w in ['卓敏科技', '紫金优选', '瀚睿科技', '红利传媒', '来吉团队', '金莎传媒']:
        return '外部团队达人分发'

    elif (x in ld_lj) and ((y in ld_dr) or (pd.isnull(y))):
        return '内部短视频投流'
    elif (x in ['3751738608588030256']) and (y in ld_dr):
        return '内部短视频投流'

    elif y in ['harry5']:

        return '内部达人分发'

    elif y in ['喜喜小助理（直播返场）']:
        return '内部达人分发'
    elif y in ['张喜喜']:
        return '内部达人分发'
    elif y in ['美食挖掘机🍜', '饱饱吃不饱', '金金会发光', '味蕾探险家', '饭桶爱说笑', '大馋丫头可可']:  # 罗欣欣运营账号
        return '内部短视频投流'

    elif y in ['啦啦啦lalala', '怪咖美食记', '爱吃的光远', '空壳少年', '食光里的馋猫🐱', '宛禾食品甄选号']:  # 白鹏飞运营账号
        return '内部短视频投流'

    elif (x in xhwh1_lj) and (y in xhwh1_dr):
        return '外部短视频投流'

    elif (x in tt1_lj) and (y in tt1_dr):
        return '内部达人分发'

    elif (x in lxx1_lj) and (y in lxx1_dr):
        return '内部达人分发'

    elif (x in lxx2_lj) and (y in lxx2_dr):
        return '内部达人分发'
    elif (x in hn1_lj) and (y in hn1_dr):
        return '外部短视频投流'
    elif (x in ['3751738608588030256']) and (y in hn1_dr):
        return '外部短视频投流'
    elif (x in lc_lj):
        return '外部短视频投流'

    elif x in ['3735010733180715297', '3741925082977665083']:  # 罗欣欣单独链接
        return '内部短视频投流'
    elif x in ['3723120728170692876', '3723119143235485902', '3741926701987398242']:  # 白鹏飞单独链接
        return '内部短视频投流'

    elif x in ['3714480675638870403', '3741923461510070306', '3550782895822129982', '3526108849595662124',
               '3699396698997653933']:
        return '内部短视频投流'

    elif (v == '商品卡') and (('lcc' in z) or ('lxx' in z)):
        return '超级商品卡'

    elif ('lcc' in z) or ('lxx' in z):
        return '内部短视频投流'

    elif (x in ['3768784577812889610']):
        return '内部达人分发'

    elif 'zs2' in z or ('zz' in z) or ('zs' in z):
        return '代播'
    elif y == '宛禾食品旗舰店直播间' or y == '宛禾牛宅':
        return '代播'
    elif y == '宛禾速食旗舰店自播间' or y == '宛禾食品旗舰店自播间':
        return '代播'

    elif y == '宛禾米线官方旗舰店' or ('z1' in z) or ('Z1' in z):
        return '自播'
    elif y == '宛禾米线速食官方旗舰店' or y == '宛禾米线' or y == '宛禾速食官方旗舰店' or y == '宛禾速食直播间' or y == '宛禾速食' or (
            'z3' in z) or ('Z3' in z):
        return '自播'
    elif y == '宛禾米线速食旗舰店' or ('lsf' in z):
        return '自播'
    elif y == '宛禾食品旗舰店' or ('z5' in z) or ('Z5' in z) or ('dsp' in z):
        return '自播'
    elif y == '宛禾速食旗舰店' or y == '宛禾食品旗舰店甄选号' or ('z6' in z) or ('Z6' in z):
        return '自播'
    elif y == '宛禾官方旗舰店' or ('z8' in z) or ('Z8' in z):
        return '自播'

    elif (v == '商品卡'):  # 投流商品卡
        return '超级商品卡'

    elif pd.notnull(y):
        return '内部达人分发'
    elif s == '精选联盟':
        return '内部达人分发'
    else:
        return '自然单'


# 应用总渠道分类
dou_res['总渠道'] = list(map(
    m,
    list(dou_res['商品ID']),
    list(dou_res['达人昵称']),
    list(dou_res['选购商品']),
    list(dou_res['广告渠道']),
    list(dou_res['流量来源']),
    list(dou_res['出单机构'])
))

# 按总渠道和投流账户聚合数据
result = dou_res.groupby([
    dou_res['总渠道'],
    dou_res['投流账户']
], as_index=False).agg(
    销售额_含优惠去退款=('成交金额_含优惠', 'sum'),
    订单数=('成交订单', 'sum'),
    退款金额=('退款金额_含优惠', 'sum')
)
result.to_excel(r'G:\工作\2025年订单\9月\绩效\9月各渠道销售额月度1.xlsx')

# SKU级别数据聚合
date_SKU = dou_res
result_sku = date_SKU.groupby([
    date_SKU['商品ID'],
    date_SKU['总渠道'],
    date_SKU['投流账户']
], as_index=False).agg(
    销售额_含优惠去退款=('成交金额_含优惠', 'sum'),
    订单数=('成交订单', 'sum'),
    退款金额=('退款金额_含优惠', 'sum')
)
result_sku.to_excel(r'G:\工作\2025年订单\9月\绩效\9月投流销售额SKU月度1.xlsx')

# 商家编码级别数据聚合
result_bm = date_SKU.groupby([
    date_SKU['商家编码'],
    date_SKU['总渠道'],
    date_SKU['投流账户']
], as_index=False).agg(
    销售额_含优惠去退款=('成交金额_含优惠', 'sum'),
    订单数=('成交订单', 'sum'),
    退款金额=('退款金额_含优惠', 'sum')
)
result_bm.to_excel(r'G:\工作\2025年订单\9月\绩效\9月投流销售额编码月度1.xlsx')

# 筛选投流绩效单量明细
dl = dou_res[
    (dou_res['成交订单'] != 0) &
    (dou_res['总渠道'] != '自播') &
    (dou_res['总渠道'] != '代播') &
    (dou_res['总渠道'] != '自然单')
    ]
dl.to_excel(r'G:\工作\2025年订单\9月\绩效\9月投流绩效单量明细月度1.xlsx')

# 利润测算
dou_li = dou_res[(dou_res['成交订单'] != 0)]
dou_ = (
    dou_li.groupby([
        dou_li['店铺'],
        dou_li['总渠道'],
        dou_li['投流账户'],
        dou_li['商品ID'],
        dou_li['商家编码'],
        dou_li['福袋'],
        dou_li['电煮锅']
    ], as_index=False)
    .agg(
        订单实付=('订单应付金额', 'sum'),
        商品数量=('商品数量', 'sum'),
        平台优惠=('平台实际承担优惠金额', 'sum'),
        达人优惠=('达人实际承担优惠金额', 'sum'),
        成交金额=('成交金额_含优惠', 'sum'),
        佣金=('预估佣金支出', 'sum'),
        招商佣金=('预估服务费收入', 'sum')
    )
)
dou_.to_excel(r'G:\工作\SKU利润测算\2025年\9月\9月利润测算明细_抖音1.xlsx')

orderid = dou_res.loc[:, ['订单id', '总渠道', '订单提交时间', '店铺']]
# 打开数据库连接
db = pymysql.connect(
    host="localhost",
    user="root",
    password="hui123456",
    db='dbtest'
)

# 使用cursor()方法创建一个游标对象cursor
cursor = db.cursor()

sqll = ('insert into orderID_qd values (%s,%s,%s,%s)')

for val in orderid.values:
    cursor.execute(sqll, tuple(val))

db.commit()
db.close()
